package com.liqiye.paymanager.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;


@RestController
@RequestMapping("/test")
public class TestController {


    @GetMapping("/poi")
    public ResponseEntity<?> poi(String month) {
        try {

            StringBuilder searchResult = new StringBuilder();
            File reportFile = new File("D:\\Edge下载\\2025-09-05 11_26_00.xlsx");
            Workbook workbook = new XSSFWorkbook(reportFile);
            // 查出有多少个sheet
            int sheetCount = workbook.getNumberOfSheets();
            for(int sheetNum = 0; sheetNum < sheetCount; sheetNum ++) {
                int cellIdx = 0;
                int rowIdx = 0;
                int totalRateRow = 0;
                Sheet sheet = workbook.getSheetAt(sheetNum);
                int rowCount = sheet.getPhysicalNumberOfRows();
                for(int rowNum = 0; rowNum < rowCount; rowNum ++) {
                    Row row = sheet.getRow(rowNum);
                    int cellCount = row.getPhysicalNumberOfCells();
                    for (int cellNum = 0; cellNum < cellCount; cellNum ++) {
                        Cell cell = row.getCell(cellNum);
                        CellType cellType = cell.getCellType();
                        if(cellType.equals(CellType.STRING)) {
                            if(cell.getStringCellValue().equalsIgnoreCase(month)) {
                                cellIdx = cellNum;
                                rowIdx = rowCount - 1;
                                break;
                            }
                        }
                    }
                    // 匹配【总费率】的行号
                    for (int cellNum = 0; cellNum < cellCount; cellNum ++) {
                        Cell cell = row.getCell(cellNum);
                        CellType cellType = cell.getCellType();
                        if(cellType.equals(CellType.STRING)) {
                            if("总费率".equals(cell.getStringCellValue())){
                                totalRateRow = rowNum;
                                break;
                            }
                        }
                    }
                }
                if(rowIdx != 0 && cellIdx != 0) {
                    Row row1 = sheet.getRow(rowIdx);
                    Cell cell1 = row1.getCell(cellIdx);
                    CellType cellType = cell1.getCellType();
                    if(cellType.equals(CellType.FORMULA)) {
                        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
                        BigDecimal resultValue = new BigDecimal(formulaEvaluator.evaluate(cell1).getNumberValue());
                        searchResult.append(sheet.getSheetName() + ":").append((new BigDecimal(resultValue.setScale(4, RoundingMode.HALF_UP).doubleValue() * 100)).setScale(2, RoundingMode.HALF_UP) + "%");
                    }
                }
                // 拼接总费率
                if(totalRateRow != 0 && cellIdx != 0) {
                    Row row = sheet.getRow(totalRateRow);
                    Cell cell = row.getCell(cellIdx);
                    CellType cellType = cell.getCellType();
                    if(cellType.equals(CellType.FORMULA)) {
                        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
                        BigDecimal resultValue = new BigDecimal(formulaEvaluator.evaluate(cell).getNumberValue());
                        searchResult.append("/").append((new BigDecimal(resultValue.setScale(4, RoundingMode.HALF_UP).doubleValue() * 100)).setScale(2, RoundingMode.HALF_UP) + "%");
                    }
                }
                searchResult.append("\n");
            }
            workbook.close();

            System.out.println("结果：");
            System.out.println(searchResult);


        } catch (Exception e) {
            e.printStackTrace();
        }


        return ResponseEntity.ok("success");
    }




    @GetMapping("/poi2")
    public ResponseEntity<?> poi2(String month) throws FileNotFoundException {

        final List<JSONObject> dataDateList = new ArrayList<>();
        final DateTimeFormatter showDateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM");
        final DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        final LocalDate beginDataLocalDate = LocalDate.parse( "2025-01-01", dateTimeFormatter);
        final LocalDate endDataLocalDate = LocalDate.parse("2025-09-01", dateTimeFormatter);

        final JSONObject jsonObject = new JSONObject();
        jsonObject.put("date", beginDataLocalDate.format(showDateTimeFormatter));
        dataDateList.add(jsonObject);
        for (int i = 1; ; i++) {
            final LocalDate localDate = beginDataLocalDate.plusMonths(i);
            if (localDate.isAfter(endDataLocalDate)) {
                break;
            }
            final JSONObject jsonObject2 = new JSONObject();
            jsonObject2.put("date", localDate.format(showDateTimeFormatter));
            dataDateList.add(jsonObject2);
        }


        FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
        File file = new File("D:\\Edge下载\\666.xlsx");
        FileOutputStream fileOutputStream = new FileOutputStream(file);
        ExcelWriter middleWriter = EasyExcel.write(file).withTemplate("D:\\Edge下载\\pp_finance1.xlsx").build();
        WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
        WriteSheet allWriteSheet = EasyExcel.writerSheet("all").build();
        middleWriter.fill(new FillWrapper("date", dataDateList), fillConfig, writeSheet);
        middleWriter.fill(new FillWrapper("date", dataDateList), fillConfig, allWriteSheet);

        middleWriter.finish();


        return ResponseEntity.ok("success");
    }








}
